﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace EWDT_CDS_Hub.App_Code
{
    public class Appeal
    {
        public int Appeal_id { get; set; }
        public String Student_id { get; set; }
        public String Appeal_type { get; set; }
        public String Appeal_status { get; set; }

        public static List<Appeal> GetAppeal()
        {
            List<Appeal> results = new List<Appeal>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Appeal";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Appeal r = new Appeal();
                    r.Appeal_id = (int)dr["appeal_id"];
                    r.Student_id = (String)dr["student_id"];
                    r.Appeal_type = (String)dr["appeal_type"];
                    r.Appeal_status = (String)dr["appeal_status"];

                    results.Add(r);
                }
            }
            catch (SqlException e)
            {
            }
            finally
            {
                conn.Close();
            }

            return results;
        }

        public static int InsertAppeal(Appeal r)
        {
            int rowsInserted = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Appeal (appeal_id, student_id, appeal_type, appeal_status) VALUES (@rid, @sid, @type, @status)";
                comm.Parameters.AddWithValue("@rid", r.Appeal_id);
                comm.Parameters.AddWithValue("@sid", r.Student_id);
                comm.Parameters.AddWithValue("@type", r.Appeal_type);
                comm.Parameters.AddWithValue("@status", r.Appeal_status);
                rowsInserted = comm.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {

            }
            finally
            {
                conn.Close();
            }

            return rowsInserted;
        }

        public static int UpdateStatus(Appeal r)
        {
            int rowsUpdated = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Appeal SET appeal_status = @status WHERE appeal_id = @id";
                comm.Parameters.AddWithValue("@id", r.Appeal_id);
                comm.Parameters.AddWithValue("@status", r.Appeal_status);
                rowsUpdated = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return rowsUpdated;
        }

        //deleted all choices in a appeal. rowback if not all rows are deleted
        public static int DeleteChoiceList(Appeal r)
        {
            int rowsDeleted = 0;

            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Choice WHERE appeal_id = @id";
                comm.Parameters.AddWithValue("@id", r.Appeal_id);
                rowsDeleted = comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return rowsDeleted;
        }

        //Retrieve all choice of a appeal
        public static List<Choice> RetrieveChoiceList(int appeal_id)
        {
            List<Choice> results = new List<Choice>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Choice WHERE appeal_id = @id";
                comm.Parameters.AddWithValue("@id", appeal_id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Choice c = new Choice();
                    c.Appeal_id = (int)dr["appeal_id"];
                    c.Subject_id = (String)dr["subject_id"];
                    c.Choice_priority = (int)dr["choice_priority"];
                    results.Add(c);
                }

                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

        public static Appeal RetrieveAppeal(int id)
        {
            Appeal r = null;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Appeal WHERE appeal_id = @id";
                comm.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    r = new Appeal();
                    r.Appeal_id = (int)dr["appeal_id"];
                    r.Student_id = (String)dr["student_id"];
                    r.Appeal_type = (String)dr["appeal_type"];
                    r.Appeal_status = (String)dr["appeal_status"];
                }

                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return r;
        }

        //retrieve all appeals of a student
        public static List<Appeal> RetrieveStudentAppeals(String student_id)
        {
            List<Appeal> results = new List<Appeal>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Appeal WHERE student_id = @id";
                comm.Parameters.AddWithValue("@id", student_id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Appeal r = new Appeal();
                    r.Appeal_id = (int)dr["appeal_id"];
                    r.Student_id = (String)dr["student_id"];
                    r.Appeal_type = (String)dr["appeal_type"];
                    r.Appeal_status = (String)dr["appeal_status"];
                    results.Add(r);
                }

                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return results;
        }



        //-------------------------------------------------------------------------------------
        //student decides to update their choices. 
        //this method retrieves the appeal, extract the list of choices of that appeal,
        //"dump" the old list and replace it with the new list.
        public static int UpdateAppealChoices(Appeal r, List<Choice> newChoiceList)
        {
            int rowsInserted = 0;

            //get old choice list
            List<Choice> oldList = RetrieveChoiceList(r.Appeal_id);

            //delete old choice list from appeal
            int rowsDeleted = DeleteChoiceList(r);

            if (rowsDeleted != 0)
            {
                foreach (Choice c in newChoiceList)
                {
                    Choice.InsertChoiceList(r, newChoiceList);
                }
            }

            return rowsInserted;
        }

    }
}